<?php
class M_student_subjects extends CI_Model {
	
	private $__table = 'student_subjects';
	
	function generate_student_subjects($enrollment_id, $section_id){
	  $sql = 'SELECT subject_id FROM schedule WHERE section_id = ?';
	  $result = $this->db->query($sql, array($section_id))->result();
	  
	  if(!empty($result)){
	    foreach($result as $key => $r):
	     $data[$key]['subject_id'] = $r->subject_id;
	     $data[$key]['enrollment_id'] = $enrollment_id;
	    endforeach;
	    
	    $this->db->where('enrollment_id',$enrollment_id)->delete($this->__table);
	    $this->db->insert_batch($this->__table, $data); 
	  }
	  
	}
	
	function get_all_by_enrollment_id($enrollment_id)
	{
	  $sql = "SELECT subjects.subj_code, subjects.subj_desc,
			  concat(schedule.time_from,' - ',schedule.time_to) as time,
			  schedule.day
            FROM student_subjects
            LEFT JOIN subjects ON subjects.subj_id = student_subjects.subject_id
			LEFT JOIN schedule ON schedule.subject_id = student_subjects.subject_id
            WHERE student_subjects.enrollment_id = ?
			GROUP BY subjects.subj_code
			ORDER BY schedule.time_from ASC";
	  
	  $query = $this->db->query($sql, $enrollment_id);
		return $query->num_rows() > 0 ? $query->result() : FALSE;
	}

	public function get_all_schedule_by_student_subjects($enroll_id = false)
	{	
		$select = "SELECT sub.subj_code,
						  sub.subj_desc,
						  concat(sch.time_from,' ',sch.time_to) as time,
						  sch.day,
						  concat(emp.first_name,' ',emp.last_name) as teacher_name
				   FROM student_subjects ss
				   LEFT JOIN enrollments e ON ss.enrollment_id = e.e_id
				   LEFT JOIN subjects sub ON sub.subj_id = ss.subject_id
				   LEFT JOIN schedule sch ON sch.subject_id = sub.subj_id AND sch.section_id = e.e_section_id
				   LEFT JOIN employees emp ON sch.teacher_id = emp.emp_id
				   WHERE ss.enrollment_id = ?";
				   
		$q = $this->db->query($select,array($enroll_id));
		return $q->num_rows() >= 1 ? $q->result() : FALSE;
	}
	
}
?>
